const { mysql: config } = require('../config')
const { mysql } = require('../qcloud')

module.exports = async (ctx) => {

  const { pageNum = 1, pageSize = 10, openId } = ctx.request.query

  const DB = mysql
  
  // const DB = require('knex')({
  //   client: 'mysql',
  //   connection: {
  //     host: config.host,
  //     port: config.port,
  //     user: config.user,
  //     password: config.pass,
  //     database: config.db,
  //     charset: config.char,
  //     multipleStatements: true
  //   }
  // })
  // 分页逻辑： 给前端的基础数据（数据总数：total， pageSize, pageNum, isLastPages, isFirstPage）
  // 两表内关联查询 sql 和 total sql
  // `select books.*, cSessionInfo.user_info from books join cSessionInfo
  //                on books.openid = cSessionInfo.open_id
  //                order by count desc limit ${pageSize} offset ${pageSize}`;
  // `select count(*) from books`;
  if (openId) {
    const books = await DB('books')
      .select('books.*', 'cSessionInfo.user_info')
      .join('cSessionInfo', 'books.openid', 'cSessionInfo.open_id')
      .orderBy('books.id', 'desc')
      .where('openid', openId)
    ctx.state.data = {
      books: books.map((book) => {
        const userInfo = JSON.parse(book.user_info)
        return Object.assign(book, {
          user_info: {
            nickName: userInfo.nickName
          }
        })
      })
    }
  } else {
    const [{ total }] = await DB('books').select().count('isbn as total')
    const pages = Math.ceil(total / pageSize)
    const isFirstPage = pageNum == 1
    const isLastPage = pageNum == pages

    const books = await DB('books')
      .select('books.*', 'cSessionInfo.user_info')
      .join('cSessionInfo', 'books.openid', 'cSessionInfo.open_id')
      .limit(pageSize)
      .offset((Number(pageNum) - 1) * 10)
      .orderBy('books.id', 'desc')
    ctx.state.data = {
      total,
      pages,
      isFirstPage,
      isLastPage,
      pageNum: Number(pageNum),
      pageSize: Number(pageSize),
      books: books.map((book) => {
        const userInfo = JSON.parse(book.user_info)
        return Object.assign(book, {
          user_info: {
            nickName: userInfo.nickName
          }
        })
      })
    }
  }
}